SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[EndAmount] ( @GroupID INT,@fromdate datetime,@todate datetime )
RETURNS @EndAmounttable TABLE
   (
	DrugId nvarchar(20),
	DrugName nvarchar(250),
	Unit nvarchar(50),
	BeginQuantity int,
	InQuantity int,
	OutQuantity int,
	AdjustQuantity int,
	EndQuantity int
   )
AS
BEGIN

 DECLARE @DrugId nvarchar(20)
 DECLARE @DrugName nvarchar(250)
 DECLARE @Unit nvarchar(50)
 DECLARE @BeginQuantity int
 DECLARE @TotalInQuantity int
 DECLARE @InQuantityAdjust int
 DECLARE @OutQuantity int
 DECLARE @OutAdjustQuantity int
 DECLARE @ReturnGoodsQuantity int 
 DECLARE @TotalOutQuantity int
 DECLARE @TotalAdjustQuantity int --Tổng hư hao
 DECLARE @EndQuantity int
 DECLARE Drug_Cursor CURSOR FOR 
		   SELECT DrugId,DrugName,Unit FROM dbo.DrugDispo where Groupid=@GroupID
		   OPEN Drug_Cursor 
		   FETCH NEXT FROM Drug_Cursor INTO @DrugId, @DrugName,@Unit
		   WHILE @@FETCH_STATUS = 0 
			BEGIN 
				--gán giá trị
				SET @BeginQuantity=ISNULL((Select Quantity from dbo.BeginAmount(@DrugId,@fromdate)),0)
				SET @InQuantityAdjust=ISNULL((Select Quantity From dbo.InAmount(@DrugId,'I',@fromdate,@todate)),0)
				SET @TotalInQuantity=ISNULL((Select Quantity from dbo.InAmount(@DrugId,'',@fromdate,@todate)),0)
				SET @OutQuantity=ISNULL((Select Quantity from dbo.OutAmount(@DrugId,@fromdate,@todate)),0)
				SET @OutAdjustQuantity=ISNULL((Select Quantity from dbo.OutAdjustAmount(@DrugId,'E',@fromdate,@todate)),0)
				SET @ReturnGoodsQuantity=ISNULL((Select Quantity from dbo.OutAdjustAmount(@DrugId,'R',@fromdate,@todate)),0)
				SET @TotalOutQuantity=@OutQuantity+@ReturnGoodsQuantity+@OutAdjustQuantity
				SET @TotalAdjustQuantity=@InQuantityAdjust+@OutAdjustQuantity
				SET @EndQuantity=(@BeginQuantity+@TotalInQuantity)-@TotalOutQuantity
				---INSERT VALUES
				INSERT INTO @EndAmounttable VALUES(
				@DrugId,
				@DrugName,
				@Unit,
				@BeginQuantity,
				@TotalInQuantity,
				@TotalOutQuantity,
				@TotalAdjustQuantity,
				@EndQuantity
				)
				FETCH NEXT FROM Drug_Cursor INTO @DrugId, @DrugName,@Unit
			END
                CLOSE Drug_Cursor
                DEALLOCATE Drug_Cursor 
Return

END
GO
